 package cnedu.ncist.similar.service;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import edu.ncist.wang.hkdf.bussiness.blo.AbstractService;
import edu.ncist.wang.hkdf.dao.database.DBConnectionException;
import edu.ncist.wang.hkdf.dao.database.DataBaseAccess;
import edu.ncist.wang.hkdf.dao.database.DataManipulationException;

public class SimilarService extends AbstractService {

	public Map doServiceProcess(Map paraMap) {
		// TODO Auto-generated method stub
		String actionType = (String)paraMap.get("ACTIONTYPE");
		
		if("similar_Similar".equals(actionType)){
			return similar_Similar(paraMap);
		}
		else if("similar_SimilarSelect".equals(actionType)){
			return similar_SimilarSelect(paraMap);
		}
		else if("similar_SimilarReport".equals(actionType)){
			return similar_SimilarReport(paraMap);
		}else if("similar_SimilarReportSelect".equals(actionType)){
			return similar_SimilarReportSelect(paraMap);
		}
		else{
			ActionTypeError();
		}
		return null;
	}
	public Map<String, Object> similar_Similar(Map paraMap){
		
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		Map<String, Object> resultMap = new HashMap<String, Object>();
		
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		
		List<Map<String, String>> similarList = null;
		List<Map<String,String>> similarList1 = null;
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		DataBaseAccess dba1 = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
	   	
		String sql ="select test_score.student_id,student_name,course_name,sum(temp.is_effort) as total_value ,total_scores,    "+
				"CASE "+
				"when total_scores>=90 then '优秀' "+
				"when total_scores>=70 then '良好' "+
				"when total_scores>=60 then '及格' "+
				"else '不及格' "+
				"end AS 'scorelevel' "+
				" from(   "+
				"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
				"  student_is_effort.job_id,student_is_effort.topic_id,   "+
				"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
				"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
				" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
				" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
				" from  "+
				" student_is_effort LEFT OUTER JOIN student_complete_status  "+
				" on student_is_effort.student_id=student_complete_status.student_id  "+
				" and student_is_effort.topic_id=student_complete_status.topic_id  "+
				"  left outer join job_is_hard   "+
				"  on student_complete_status.student_id=job_is_hard.student_id   "+
				"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
				"  left outer join student   "+
				"  on job_is_hard.student_id=student.student_id    "+
				"  where    "+
				"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
				 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
				"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
				"WHERE  "+
				"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
				"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' "+
				"GROUP BY temp.student_id  ";
	   	
		String sql1 =  "select count(*) as topic_amount FROM topic  "+
				" where belong_course_id='"+courseId+"'  "+
				"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
		
		try{
	   		similarList = dba.getMultiRecord(sql);
	   		similarList1  = dba1.getMultiRecord(sql1);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get test info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		
		if(null == similarList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		
		resultMap.put("SIMILARINFO", similarList);
		resultMap.put("TOPICAMOUNT", similarList1);
		resultMap.put("CONDITIONMAP", map);
		System.out.print("similarLit="+similarList);
		System.out.print("similarLit1="+similarList1);
		return resultMap;
		
	}
	
	public Map<String,Object> similar_SimilarSelect(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		
		String name = map.get("STU_NAME").toString();
		String stuid = map.get("STU_ID").toString();
		
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>> similarList = null;
		List<Map<String,String>> similarList1 = null;
		
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		DataBaseAccess dba1 = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		
		String sql = null;
		String sql1 = null;
		
		//瀛﹀彿鍜屽鍚嶉兘涓虹┖
				if ((stuid.equals(null) || stuid.equals("")) && 
						(name.equals(null) || name.equals(""))){
					
					sql ="select test_score.student_id,student_name,course_name,sum(temp.is_effort) as total_value ,total_scores,    "+
							"CASE "+
							"when total_scores>=90 then '优秀' "+
							"when total_scores>=70 then '良好' "+
							"when total_scores>=60 then '及格' "+
							"else '不及格' "+
							"end AS 'scorelevel' "+
							" from(   "+
							"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
							"  student_is_effort.job_id,student_is_effort.topic_id,   "+
							"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
							"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
							" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
							" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
							" from  "+
							" student_is_effort LEFT OUTER JOIN student_complete_status  "+
							" on student_is_effort.student_id=student_complete_status.student_id  "+
							" and student_is_effort.topic_id=student_complete_status.topic_id  "+
							"  left outer join job_is_hard   "+
							"  on student_complete_status.student_id=job_is_hard.student_id   "+
							"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
							"  left outer join student   "+
							"  on job_is_hard.student_id=student.student_id    "+
							"  where    "+
							"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
							 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
							"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
							"WHERE  "+
							"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
							"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' "+
							"GROUP BY temp.student_id  ";
				   	
					 sql1 =  "select count(*) as topic_amount FROM topic  "+
							" where belong_course_id='"+courseId+"'  "+
							"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
				}
				//瀛﹀彿涓虹┖浣嗗鍚嶄笉涓虹┖
				else if((stuid.equals(null) || stuid.equals("")) &&!name.equals(null) && !name.equals(""))
				{
					sql ="select test_score.student_id,student_name,course_name,sum(temp.is_effort) as total_value ,total_scores,    "+
							"CASE "+
							"when total_scores>=90 then '优秀' "+
							"when total_scores>=70 then '良好' "+
							"when total_scores>=60 then '及格' "+
							"else '不及格' "+
							"end AS 'scorelevel' "+
							" from(   "+
							"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
							"  student_is_effort.job_id,student_is_effort.topic_id,   "+
							"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
							"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
							" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
							" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
							" from  "+
							" student_is_effort LEFT OUTER JOIN student_complete_status  "+
							" on student_is_effort.student_id=student_complete_status.student_id  "+
							" and student_is_effort.topic_id=student_complete_status.topic_id  "+
							"  left outer join job_is_hard   "+
							"  on student_complete_status.student_id=job_is_hard.student_id   "+
							"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
							"  left outer join student   "+
							"  on job_is_hard.student_id=student.student_id    "+
							"  where    "+
							"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
							 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
							"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
							"WHERE  "+
							"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
							"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' and temp.student_name='"+name+"' "+
							"GROUP BY temp.student_id  ";
				   	
					 sql1 =  "select count(*) as topic_amount FROM topic  "+
							" where belong_course_id='"+courseId+"'  "+
							"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
				}
				//濮撳悕涓虹┖瀛﹀彿涓嶄负绌�
				else if(!stuid.equals(null) && !stuid.equals("")&&(name.equals(null) || name.equals(""))){
					sql ="select test_score.student_id,student_name,course_name,sum(temp.is_effort) as total_value ,total_scores,    "+
							"CASE "+
							"when total_scores>=90 then '优秀' "+
							"when total_scores>=70 then '良好' "+
							"when total_scores>=60 then '及格' "+
							"else '不及格' "+
							"end AS 'scorelevel' "+
							" from(   "+
							"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
							"  student_is_effort.job_id,student_is_effort.topic_id,   "+
							"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
							"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
							" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
							" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
							" from  "+
							" student_is_effort LEFT OUTER JOIN student_complete_status  "+
							" on student_is_effort.student_id=student_complete_status.student_id  "+
							" and student_is_effort.topic_id=student_complete_status.topic_id  "+
							"  left outer join job_is_hard   "+
							"  on student_complete_status.student_id=job_is_hard.student_id   "+
							"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
							"  left outer join student   "+
							"  on job_is_hard.student_id=student.student_id    "+
							"  where    "+
							"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
							 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
							"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
							"WHERE  "+
							"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
							"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' and temp.student_id='"+stuid+"' "+
							"GROUP BY temp.student_id  ";
				   	
					 sql1 =  "select count(*) as topic_amount FROM topic  "+
							" where belong_course_id='"+courseId+"'  "+
							"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
				}
				//濮撳悕鍜屽鍙烽兘涓嶄负绌�
				else if(!stuid.equals(null) && !stuid.equals("")&&!name.equals(null) && !name.equals("")){
					sql ="select test_score.student_id,student_name,course_name,sum(temp.is_effort) as total_value ,total_scores,    "+
							"CASE "+
							"when total_scores>=90 then '优秀' "+
							"when total_scores>=70 then '良好' "+
							"when total_scores>=60 then '及格' "+
							"else '不及格' "+
							"end AS 'scorelevel' "+
							" from(   "+
							"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
							"  student_is_effort.job_id,student_is_effort.topic_id,   "+
							"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
							"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
							" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
							" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
							" from  "+
							" student_is_effort LEFT OUTER JOIN student_complete_status  "+
							" on student_is_effort.student_id=student_complete_status.student_id  "+
							" and student_is_effort.topic_id=student_complete_status.topic_id  "+
							"  left outer join job_is_hard   "+
							"  on student_complete_status.student_id=job_is_hard.student_id   "+
							"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
							"  left outer join student   "+
							"  on job_is_hard.student_id=student.student_id    "+
							"  where    "+
							"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
							 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
							"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
							"WHERE  "+
							"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
							"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' "+
							"and temp.student_id = '"+stuid+"' and temp.student_name = '"+name+"'"+
							"GROUP BY temp.student_id  ";
				   	
					 sql1 =  "select count(*) as topic_amount FROM topic  "+
							" where belong_course_id='"+courseId+"'  "+
							"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
					
				}
				
				
				try{
					similarList = dba.getMultiRecord(sql);
					similarList1 = dba1.getMultiRecord(sql1);
					dba.close();
					dba1.close();
				}
				catch(DataManipulationException e){
					System.out.println("get test info access database error!");
					e.printStackTrace();
				}
				catch(DBConnectionException e){
					System.out.println("database close error!");
					e.printStackTrace();
				}
				
				if(null == similarList){
					resultMap.put("RESULT", "fail");
				}
				else{
					resultMap.put("RESULT", "success");
				}
				resultMap.put("SIMILARINFO", similarList);
				resultMap.put("TOPICAMOUNT", similarList1);
				resultMap.put("CONDITIONMAP", map);
				
		
		return resultMap;
	}
	
	//鑰冭瘯-浣滀笟鐩稿叧鑱斿垎鏋愭姤鍛�
	public Map<String, Object> similar_SimilarReport(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>> similarList = null;
		List<Map<String,String>> similarList1 = null;
		
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		DataBaseAccess dba1 = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		String sql ="select temp.student_id,temp.student_name,course_name,"+
				"round((sum(temp.is_effort)),2) as total_value,"+
				"total_scores,round((total_scores-(sum(temp.is_effort)/24*100)),2) as similar_value,"+
				"case "+
				"when (total_scores-(sum(temp.is_effort)/24*100))>50 THEN '大'  "+
				"when (total_scores-(sum(temp.is_effort)/24*100))>30 THEN '较大' "+
				"when (total_scores-(sum(temp.is_effort)/24*100))>15 THEN '小' "+
				"else '较小' "+
				"END AS 'similar'"+
				" from(   "+
				"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
				"  student_is_effort.job_id,student_is_effort.topic_id,   "+
				"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
				"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
				" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
				" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
				" from  "+
				" student_is_effort LEFT OUTER JOIN student_complete_status  "+
				" on student_is_effort.student_id=student_complete_status.student_id  "+
				" and student_is_effort.topic_id=student_complete_status.topic_id  "+
				"  left outer join job_is_hard   "+
				"  on student_complete_status.student_id=job_is_hard.student_id   "+
				"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
				"  left outer join student   "+
				"  on job_is_hard.student_id=student.student_id    "+
				"  where    "+
				"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
				 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
				"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
				"WHERE  "+
				"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
				"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' "+
				"GROUP BY temp.student_id  ";
		String sql1 = "select count(*) as topic_amount FROM topic  "+
				" where belong_course_id='"+courseId+"'  "+
				"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
		
	   	try{
	   		similarList = dba.getMultiRecord(sql);
	   		similarList1 = dba1.getMultiRecord(sql1);
			dba.close();
		}
		catch(DataManipulationException e){
			System.out.println("get test info access database error!");
			e.printStackTrace();
		}
		catch(DBConnectionException e){
			System.out.println("database close error!");
			e.printStackTrace();
		}
		
		if(null == similarList){
			resultMap.put("RESULT", "fail");
		}
		else{
			resultMap.put("RESULT", "success");
		}
		
		resultMap.put("SIMILARINFO", similarList);
		resultMap.put("TOPICAMOUNT", similarList1);
		resultMap.put("CONDITIONMAP", map);
		
		return resultMap;
		
	}
	public Map<String,Object> similar_SimilarReportSelect(Map paraMap){
		HttpServletRequest request = (HttpServletRequest)paraMap.get("REQUEST");
		Map map = (Map)paraMap.get("CONDITIONMAP");
		
		String name = map.get("STU_NAME").toString();
		String stuid = map.get("STU_ID").toString();
		
		Map<String, Object> resultMap = new HashMap<String, Object>();
		List<Map<String, String>> similarList = null;
		List<Map<String,String>> similarList1 = null;
		
		HttpSession session = request.getSession();
		String courseId=session.getAttribute("COURSEID").toString();
		String classId=session.getAttribute("CLASSID").toString();
		
		DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		DataBaseAccess dba1 = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
		
		String sql = null;
		String sql1 = null;
		
		//瀛﹀彿鍜屽鍚嶉兘涓虹┖
				if ((stuid.equals(null) || stuid.equals("")) && 
						(name.equals(null) || name.equals(""))){
					
					sql ="select temp.student_id,temp.student_name,course_name,"+
							"(sum(temp.is_effort)) as total_value,"+
							"total_scores,(total_scores-(sum(temp.is_effort)/24*100)) as similar_value,"+
							"case "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>50 THEN '大'  "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>30 THEN '较大' "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>15 THEN '小' "+
							"else '较小' "+
							"END AS 'similar'"+
							" from(   "+
							"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
							"  student_is_effort.job_id,student_is_effort.topic_id,   "+
							"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
							"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
							" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
							" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
							" from  "+
							" student_is_effort LEFT OUTER JOIN student_complete_status  "+
							" on student_is_effort.student_id=student_complete_status.student_id  "+
							" and student_is_effort.topic_id=student_complete_status.topic_id  "+
							"  left outer join job_is_hard   "+
							"  on student_complete_status.student_id=job_is_hard.student_id   "+
							"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
							"  left outer join student   "+
							"  on job_is_hard.student_id=student.student_id    "+
							"  where    "+
							"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
							 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
							"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
							"WHERE  "+
							"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
							"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' "+
							"GROUP BY temp.student_id  ";
				   	
					 sql1 =  "select count(*) as topic_amount FROM topic  "+
							" where belong_course_id='"+courseId+"'  "+
							"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
				}
				//瀛﹀彿涓虹┖浣嗗鍚嶄笉涓虹┖
				else if((stuid.equals(null) || stuid.equals("")) &&!name.equals(null) && !name.equals(""))
				{
					sql ="select temp.student_id,temp.student_name,course_name,"+
							"(sum(temp.is_effort)) as total_value,"+
							"total_scores,(total_scores-(sum(temp.is_effort)/24*100)) as similar_value,"+
							"case "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>50 THEN '大'  "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>30 THEN '较大' "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>15 THEN '小' "+
							"else '较小' "+
							"END AS 'similar'"+
							" from(   "+
							"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
							"  student_is_effort.job_id,student_is_effort.topic_id,   "+
							"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
							"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
							" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
							" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
							" from  "+
							" student_is_effort LEFT OUTER JOIN student_complete_status  "+
							" on student_is_effort.student_id=student_complete_status.student_id  "+
							" and student_is_effort.topic_id=student_complete_status.topic_id  "+
							"  left outer join job_is_hard   "+
							"  on student_complete_status.student_id=job_is_hard.student_id   "+
							"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
							"  left outer join student   "+
							"  on job_is_hard.student_id=student.student_id    "+
							"  where    "+
							"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
							 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
							"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
							"WHERE  "+
							"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
							"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' "+
									"and temp.student_name='"+name+"'"+
							"GROUP BY temp.student_id  ";
				   	
					 sql1 =  "select count(*) as topic_amount FROM topic  "+
							" where belong_course_id='"+courseId+"'  "+
							"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
				}
				//濮撳悕涓虹┖瀛﹀彿涓嶄负绌�
				else if(!stuid.equals(null) && !stuid.equals("")&&(name.equals(null) || name.equals(""))){
					sql ="select temp.student_id,temp.student_name,course_name,"+
							"(sum(temp.is_effort)) as total_value,"+
							"total_scores,(total_scores-(sum(temp.is_effort)/24*100)) as similar_value,"+
							"case "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>50 THEN '大'  "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>30 THEN '较大' "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>15 THEN '小' "+
							"else '较小' "+
							"END AS 'similar'"+
							" from(   "+
							"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
							"  student_is_effort.job_id,student_is_effort.topic_id,   "+
							"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
							"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
							" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
							" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
							" from  "+
							" student_is_effort LEFT OUTER JOIN student_complete_status  "+
							" on student_is_effort.student_id=student_complete_status.student_id  "+
							" and student_is_effort.topic_id=student_complete_status.topic_id  "+
							"  left outer join job_is_hard   "+
							"  on student_complete_status.student_id=job_is_hard.student_id   "+
							"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
							"  left outer join student   "+
							"  on job_is_hard.student_id=student.student_id    "+
							"  where    "+
							"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
							 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
							"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
							"WHERE  "+
							"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
							"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' "
									+ "and temp.student_id='"+stuid+"' "+
							"GROUP BY temp.student_id  ";
				   	
					 sql1 =  "select count(*) as topic_amount FROM topic  "+
							" where belong_course_id='"+courseId+"'  "+
							"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
				}
				//濮撳悕鍜屽鍙烽兘涓嶄负绌�
				else if(!stuid.equals(null) && !stuid.equals("")&&!name.equals(null) && !name.equals("")){
					sql ="select temp.student_id,temp.student_name,course_name,"+
							"(sum(temp.is_effort)) as total_value,"+
							"total_scores,(total_scores-(sum(temp.is_effort)/24*100)) as similar_value,"+
							"case "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>50 THEN '大'  "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>30 THEN '较大' "+
							"when (total_scores-(sum(temp.is_effort)/24*100))>15 THEN '小' "+
							"else '较小' "+
							"END AS 'similar'"+
							" from(   "+
							"  select student.student_name,student_is_effort.student_id,student_is_effort.course_id,    "+
							"  student_is_effort.job_id,student_is_effort.topic_id,   "+
							"  student_is_effort.is_many_times_to_submit,student_is_effort.is_deadline,   "+
							"  student_complete_status.submit_statu,job_is_hard.scorer_index,      "+
							" (student_is_effort.is_many_times_to_submit+student_is_effort.is_deadline+    "+
							" student_complete_status.submit_statu+job_is_hard.scorer_index) as is_effort    "+
							" from  "+
							" student_is_effort LEFT OUTER JOIN student_complete_status  "+
							" on student_is_effort.student_id=student_complete_status.student_id  "+
							" and student_is_effort.topic_id=student_complete_status.topic_id  "+
							"  left outer join job_is_hard   "+
							"  on student_complete_status.student_id=job_is_hard.student_id   "+
							"  and student_complete_status.topic_id=job_is_hard.topic_id   "+
							"  left outer join student   "+
							"  on job_is_hard.student_id=student.student_id    "+
							"  where    "+
							"  student_is_effort.course_id='"+courseId+"' and student_is_effort.class_id='"+classId+"'    "+
							 " and student_is_effort.job_id=(select job_id from student_is_effort where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)     "+
							"  order by student_is_effort.course_id,student_is_effort.job_id,student_is_effort.student_id,student_is_effort.topic_id ASC) temp   ,course,test_score  "+
							"WHERE  "+
							"test_score.student_id=temp.student_id and course.course_id=temp.course_id "+
							"and test_score.chose_course_id='"+courseId+"' and test_score.course_class_id='"+classId+"' "
									+ "and temp.student_id='"+stuid+"' and temp.student_name='"+name+"'"+
							"GROUP BY temp.student_id  ";
					 sql1 =  "select count(*) as topic_amount FROM topic  "+
							" where belong_course_id='"+courseId+"'  "+
							"  and belong_job_id=(select job_id from student_is_effort   where course_id='"+courseId+"'   and class_id='"+classId+"'     order by job_id desc  limit 1)";
					
				}
				
				
				try{
					similarList = dba.getMultiRecord(sql);
					similarList1 = dba1.getMultiRecord(sql1);
					dba.close();
					dba1.close();
				}
				catch(DataManipulationException e){
					System.out.println("get test info access database error!");
					e.printStackTrace();
				}
				catch(DBConnectionException e){
					System.out.println("database close error!");
					e.printStackTrace();
				}
				
				if(null == similarList){
					resultMap.put("RESULT", "fail");
				}
				else{
					resultMap.put("RESULT", "success");
				}
				resultMap.put("SIMILARINFO", similarList);
				resultMap.put("TOPICAMOUNT", similarList1);
				resultMap.put("CONDITIONMAP", map);
				
		
		return resultMap;
	}
}
